Imports System.Data.OleDb

''' <summary>
''' Class to provide very fast importing of Excel files
''' </summary>
Public Class clsDataExcelOld
    Inherits clsData

    Private mConnection As OleDb.OleDbConnection
    Private mCommand As OleDbCommand
    Private mDataReader As OleDb.OleDbDataReader
    Private mNumRecords As Integer
    Private mExcelFile As String


    ''' <summary>
    ''' Instantiate clsDataExcel
    ''' </summary>
    ''' <param name="_Filename">Name of Excel file to read</param>
    ''' <param name="_HasHeaders">True if first row contains column headers for field names</param>
    Public Overloads Sub Initialize(ByVal _Filename As String, ByVal _HasHeaders As Boolean)
        MyBase.Initialize(_Filename)
        mDataType = enumDataType.OleDB
        mExcelFile = _Filename
        mHasHeaders = _HasHeaders
        If Not My.Computer.FileSystem.FileExists(mExcelFile) Then Throw New IO.FileNotFoundException : Exit Sub

        Dim csb As New OleDbConnectionStringBuilder
        With csb

            'note: due to bug in driver, cannot force Excel to read more rows; only workaround according to http://support.microsoft.com/kb/189897 is
            'to set the registry using regedit (cannot do programmatically)

            If IO.Path.GetExtension(_Filename).ToUpper = ".XLS" Then
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Item("Extended Properties") = String.Format("Excel 8.0;HDR={0};IMEX=1;MAXSCANROWS=15;READONLY=FALSE", IIf(_HasHeaders, "Yes", "No"))
            Else
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .Item("Extended Properties") = String.Format("Excel 12.0;HDR={0};IMEX=1;MAXSCANROWS=15;READONLY=FALSE", IIf(_HasHeaders, "Yes", "No"))
            End If
            .DataSource = _Filename  'should be path and xls filename (note: table names should be formatted like [Sheet1$]
            mConnection = New OleDbConnection(.ConnectionString)
        End With

        If Not OpenConnection() Then Exit Sub

        Dim SchemaTable As DataTable
        SchemaTable = mConnection.GetSchema("Tables")

        mTableNames = New Generic.List(Of String)
        For Each row As DataRow In SchemaTable.Rows
            Dim TableName As String = row.Item("Table_Name")
            TableName = Proper(TableName).Replace("'", "").Replace("$", "")
            Dim IsUserTable As Boolean
            Dim TableType As String = row.Item("Table_Type")
            IsUserTable = TableType.Equals("TABLE", StringComparison.OrdinalIgnoreCase) Or TableType.Equals("BASE TABLE", StringComparison.OrdinalIgnoreCase)
            If IsUserTable And Not mTableNames.Contains(TableName) Then mTableNames.Add(TableName)
        Next
        mConnection.Close()

        ActiveTable = mTableNames(0)
    End Sub

    Public Overridable Function OpenConnection() As Boolean
        While mConnection.State = ConnectionState.Connecting
            Threading.Thread.Sleep(500)
        End While
        If mConnection.State <> ConnectionState.Open Then
            Try
                mConnection.Open()
                Return True
            Catch ex As Exception
                Throw New applicationexception("Unable to open Excel file; this may be due to the file being opened by another user (or yourself).", ex)
                Return False
            End Try
        Else
            Return True
        End If
    End Function

    Public Overrides Property ActiveTable() As String
        Get
            Return MyBase.ActiveTable
        End Get
        Set(ByVal value As String)
            value = "[" & RemoveBrackets(value) & "]"
            MyBase.ActiveTable = value
            Rewind()
        End Set
    End Property

    ''' <summary>
    ''' Read next line from file into memory
    ''' </summary>
    ''' <returns>True if successful</returns>
    Public Overrides Function Read() As Boolean
        Try
            If Not MyBase.Read() Then Return False
            If mDataReader.Read Then
                mItems.Clear()
                For i As Integer = 0 To mDataReader.FieldCount - 1
                    mItems.Add(TestNull(mDataReader.GetValue(i), ""))
                Next
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            ErrorMsg(, ex)
            Return False
        End Try
    End Function

    Public Overrides Sub Rewind()
        MyBase.Rewind()
        If mDataReader IsNot Nothing Then mDataReader.Close()
        mConnection.Dispose()

        Dim csb As New OleDbConnectionStringBuilder
        With csb
            '.Item("Extended Properties") = "Excel 8.0" ';HDR=Yes;IMEX=1"
            If IO.Path.GetExtension(mFilename).ToUpper = ".XLS" Then
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Item("Extended Properties") = String.Format("Excel 8.0;HDR={0};IMEX=1;MAXSCANROWS=15;READONLY=FALSE", IIf(mHasHeaders, "Yes", "No"))
            Else
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .Item("Extended Properties") = String.Format("Excel 12.0;HDR={0};IMEX=1;MAXSCANROWS=15;READONLY=FALSE", IIf(mHasHeaders, "Yes", "No"))
            End If
            .DataSource = mFilename  'should be path and xls filename (note: table names should be formatted like [Sheet1$])
            mConnection = New OleDbConnection(.ConnectionString)
        End With

        'get number of records and open datareader
        If OpenConnection() Then
            Dim cmd As New OleDbCommand(String.Format("SELECT COUNT(*) FROM [{0}$]", MyBase.ActiveTable), mConnection)
            mNumRecords = cmd.ExecuteScalar
            cmd.Dispose()
            mCommand = New OleDbCommand(String.Format("SELECT * FROM [{0}$]", MyBase.ActiveTable), mConnection)
            mDataReader = mCommand.ExecuteReader
            FieldNames.Clear()
            For i As Integer = 0 To mDataReader.FieldCount - 1
                FieldNames.Add(mDataReader.GetName(i))
            Next
        End If
    End Sub

    Public Overrides Function PercentComplete() As Double
        Return CInt(mRecordNum * 100.0 / mNumRecords)
    End Function

    Public Overrides Sub Close()
        MyBase.Close()
        mDataReader.Close()
    End Sub

    Private disposed As Boolean = False

    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposed Then
            If disposing Then
                MyBase.Dispose(disposing)
                mCommand.Dispose()
                mConnection.Close()
                mConnection.Dispose()
            End If
        End If
        Me.disposed = True
    End Sub
End Class
